/*====================================================================================
                                                                     
		          O-Ring Production Networks
			        By Demir-Fieler-Xu-Yang
  
*====================================================================================*/	

clear all
set more off
log close _all
set matsize 5000

cd "\data\" // Add your directory here


/***************************************
/Load firm registry data
****************************************/
use "Girisim Sicil.dta",clear

rename sırano ID
rename sırano_donem year

keep if year>2008 & year<2016

rename nacesinifkod nace4
gen nace2=floor(nace4/100)
//Drop agriculture, mining, utilities, financial services, public services, health and education 
drop if nace2<10
drop if nace2>33 & nace2<44
drop if nace2>63 & nace2<67
drop if nace2>82

drop if estabyear<1923

rename ılplakakodu province_code
rename ilçead sub_province

for X in num 1/4: replace calisansayisi_Xceyrek=. if calisansayisi_Xceyrek==0
for X in num 1/4: gen avgwageX=toplamucret_Xceyrek/calisansayisi_Xceyrek
egen workers=rowmean(calisansayisi_1ceyrek-calisansayisi_4ceyrek)
egen wagebill=rowtotal(toplamucret_1ceyrek-toplamucret_4ceyrek) //quarterly wages
replace wagebill=3*wagebill // annual wages
gen avgwage=wagebill/workers

bys nace2 province_code year,sort: egen avgwage_rst=mean(avgwage)
gen avgwage_adj=avgwage/avgwage_rst

keep ID nace4 nace2 province_code sub_province  year avgwage workers wagebill avgwage_adj

destring ID,replace
sort ID year

save temp1.dta,replace

/***************************************
/Load balance sheet data
****************************************/

use "Bilanco.dta",clear

rename sırano ID
rename sırano_donem year

keep if year>2008 & year<2016
keep ID year abrütsatýþlar yurtiçisatýþlar
rename abrütsatýþlar sales
rename yurtiçisatýþlar domesticsales   
destring sales,replace

destring ID,replace

//Merge with firm registry data
sort ID year
merge ID year using temp1.dta
keep if _merge==3 // keep firms that exist in both datasets
drop _merge

bys nace4 year,sort: egen sales_ind=sum(sales)
gen mktshare=sales/sales_ind
drop sales_ind

sort ID year
save temp2.dta,replace


/***************************************
/Load VAT data
****************************************/

use "BABS.dta",clear 

rename sırano_alici buyer_ID
rename sırano_satici seller_ID
rename sırano_donem year
rename deger value

keep if year>2008 & year<2016 
collapse (sum) value,by(seller_ID buyer_ID year)

//Construct master data //
gen  ID=buyer_ID
destring ID,replace
sort ID year
merge ID year using "temp2.dta"
keep if _merge==3 // keep buyers with firm registry info
drop _merge ID  
for X in any nace4 nace2 province_code sub_province avgwage workers wagebill avgwage_adj sales mktshare domesticsales: rename X X_b

gen  ID=seller_ID
destring ID,replace
sort ID year
merge ID year using "temp2.dta"
keep if _merge==3 // keep suppliers with firm registry info
drop _merge ID  
for X in any nace4 nace2 province_code sub_province avgwage workers wagebill avgwage_adj sales mktshare domesticsales: rename X X_s

save masterdata.dta,replace


// Manufacturing sample in 2015 //

*Construct outdegree
use masterdata.dta,clear

keep if year==2015

keep if nace2_b <34 // Keep manufacturing buyers
keep if nace2_s <34 // Keep manufacturing suppliers
drop if avgwage_adj_b==0 | avgwage_adj_b==.
drop if avgwage_adj_s==0 | avgwage_adj_s==.

bys seller_ID ,sort: gen outdegree=_N

collapse (mean) outdegree,by(seller_ID)
rename seller_ID ID
sort ID 
save temp3.dta,replace


use masterdata.dta,clear

keep if year==2015

keep if nace2_b <34 // Keep manufacturing buyers
keep if nace2_s <34 // Keep manufacturing suppliers
drop if avgwage_adj_b==0 | avgwage_adj_b==.
drop if avgwage_adj_s==0 | avgwage_adj_s==.

for X in any avgwage_adj_s avgwage_adj_b: replace X=ln(X) 

bys buyer_ID,sort: egen total_value_b=sum(value)
gen value_share=value/total_value_b

gen supavgwage_w=avgwage_adj_s*value_share

bys buyer_ID ,sort: gen indegree=_N
bys buyer_ID ,sort: egen supavgwage_uw=mean(avgwage_adj_s)
gen supavgwage_cov=(avgwage_adj_s-supavgwage_uw)*(value_share-1/indegree)

collapse (mean) avgwage_adj_b supavgwage_uw  workers_b wagebill_b sales_b mktshare_b indegree  (sum) supavgwage_w supavgwage_cov,by(buyer_ID nace4_b nace2_b province_code_b sub_province_b  )

gen ID=buyer_ID
sort ID 
merge ID using temp3.dta
drop _m ID
capture erase temp3.dta

keep if supavgwage_uw!=.

su avgwage_adj_b,det
keep if avgwage_adj_b>r(p1) & avgwage_adj_b<r(p99)

save manufsample_2015.dta,replace

*Construct exports
use "ihracat.dta",clear

rename sırano ID
rename sırano_donem year
rename kýymet exports

keep if year==2015

collapse (sum) exports,by(ID)

destring ID,replace
sort ID
save temp3.dta,replace

*Merge with data on manufacturing sample in 2015
use manufsample_2015,clear
gen ID=buyer_ID
destring ID,replace
sort ID 
merge ID using temp3.dta
drop if _m==2
drop _m ID
replace exports=0 if exports==.
capture erase temp3.dta

save manufsample_2015.dta,replace

*Construct imports
use "ithalat.dta",clear

rename sırano ID
rename sırano_donem year
rename kýymet imports

keep if year==2015

collapse (sum) imports,by(ID)

destring ID,replace
sort ID
save temp3.dta,replace

*Merge with data on manufacturing sample in 2015
use manufsample_2015,clear
gen ID=buyer_ID
destring ID,replace
sort ID 
merge ID using temp3.dta
drop if _m==2
drop _m 
replace imports=0 if imports==.
capture erase temp3.dta

* Identify firms with foreign ownership

sort ID 
merge ID using "ForeignOwn_Snapshot.dta"
drop if _m==2
drop _m ID
replace foreign=0 if foreign==.

sort buyer_ID

save manufsample_2015.dta,replace


/***************************************
/Load linked employer-employee data
****************************************/

use "CALISAN_MIKRO_2012_2015",clear

replace sırano_donem=subinstr(sırano_donem,"_"," ",.)
split sırano_donem 
rename sırano_donem1 ID
rename donem_yıl year
rename donem_ay month
rename kazanc earnings
rename prım_gun_sayısı numofpremiumdays
rename fkyas age
rename cinsiyetaciklama gender
rename calisansgk employee_ID

destring ID, replace
replace earnings=subinstr(earnings,",",".",.)
destring earnings, replace

for X in any tanım : replace tanım=subinstr(tanım,"_"," ",.)
rename tanım1 occupation_code
destring occupation_code, replace

drop if numofpremiumdays<28 // Keep full-time workers
drop if numofpremiumdays>31
drop if gender==0

save temp3.dta,replace 

//Worker fixed effects //
keep if month==12 // December as the reference period

sort ID year
merge ID year using temp2.dta
drop if _m==2
drop _m

egen f=group(ID)
egen w=group(employee_ID)
egen gt=group(gender year)
egen srt=group(nace2 province_code year)

gen lnearnings=ln(earnings)
gen age2=age^2
gen size=ln(sales)

reghdfe lnearnings age2 size ,absorb(w_fe=w f srt gt) 

collapse (mean) w_fe,by(ID year)

sort ID year
save wfe2.dta,replace

// Non-routine skill intensity //

use temp3.dta,clear

keep if year==2015
collapse (count)  employee_ID,by(occupation_code ID)
rename employee_ID numworkers_o
bys ID ,sort: egen numworkers=sum(numworkers_o)

gen isco08=occupation_code
tostring isco08,replace
sort isco08
merge isco08 using "Skills_NRA_NRI.dta"
drop _m

gen shrworkers_o=numworkers_o/numworkers
gen skill_o=0.5*(NRA +NRI)
gen skills_NR=shrworkers_o*skill_o

unique occupation_code, by(ID) gen(numocc)
 
collapse (sum) skills_NR (mean) numocc,by(ID )

sort ID 
save skills_other.dta,replace

// Residual Wages and Occupation Fixed Effects //

use temp3.dta,clear

egen f=group(ID)
egen gt=group(gender year)
egen srt=group(nace2 province_code year)

gen lnearnings=ln(earnings)
gen age2=age^2
gen size=ln(sales)

reghdfe lnearnings age2 size ,absorb(o_fe=occupation_code srt gt)  res(reswages)

keep if year==2015

collapse (count)  employee_ID (mean) o_fe reswages,by(occupation_code ID)

rename employee_ID numworkers_o
bys ID ,sort: egen numworkers=sum(numworkers_o)
gen shrworkers_o=numworkers_o/numworkers

gen occ_fe_w=shrworkers_o*o_fe


collapse (mean) reswages (sum) occ_fe_w,by(ID )

sort ID 
save reswage_occfe.dta,replace

capture erase temp3.dta

******************************************************************************************************

/***************************************
/Construct firm-level trade shocks 
****************************************/

use "GDPpc_WDI.dta",clear
keep if seriesname=="GDP per capita (constant 2010 US$)"
keep if year==2010
gen lngdppc=ln(d )
collapse (mean) lngdppc,by(countrycode)
sort countrycode 
save temp3.dta,replace

use "GDP_WDI.dta",clear
keep if year==2010
gen lngdp=ln(gdp )
collapse (mean) lngdp,by(countrycode)
sort countrycode 
merge countrycode using temp3.dta
sort countrycode
save temp3.dta,replace

// Construct income-adjusted and unadjusted WID and WES

use "TradeData_UNCOMTRADE_20112015.dta",clear
keep if tradeflow=="Import"

keep countrycode hs4 year value 

reshape wide  value  ,i(countrycode hs4) j(year)

sort countrycode 
merge countrycode  using temp3
keep if _merge==3
drop _m

forvalues i=2011(1)2015 {
gen adjWID`i'=ln(value`i')*lngdppc
gen gdpadjWID`i'=ln(value`i')*lngdp   
gen unadjWID`i'=ln(value`i')
}

drop value*
sort countrycode hs4
save temp4.dta,replace

use "TradeData_UNCOMTRADE_20112015.dta",clear
keep if tradeflow=="Export"

keep countrycode hs4 year value 

reshape wide  value  ,i(countrycode hs4) j(year)

sort countrycode 
merge countrycode  using temp3
keep if _merge==3
drop _m
capture erase temp3.dta

forvalues i=2011(1)2015 {
gen adjWES`i'=ln(value`i')*lngdppc
gen unadjWES`i'=ln(value`i')
}

drop value* 
sort countrycode hs4
save temp5.dta,replace


//Firm-level export/import shocks

*Construct exports
use "ihracat.dta",clear

rename sırano ID
rename sırano_donem year
rename kýymet exports

keep if year==2010

collapse (sum) exports,by(ID)

destring ID,replace
sort ID
save temp3.dta,replace

*Construct imports
use "ithalat.dta",clear

rename sırano ID
rename sırano_donem year
rename kýymet imports

keep if year==2010

collapse (sum) imports,by(ID)

destring ID,replace
sort ID
merge ID using temp3
save temp3.dta,replace

*Construct export shocks
use masterdata.dta,clear

keep if year==2010

keep if nace2_b<34

collapse (sum) value ,by(seller_ID)

gen rename seller_ID ID
destring ID,replace

sort ID
merge ID using temp3
drop if _m==2
drop imports _m

replace exports=0 if exports==.

gen expintensity=1.5*exports/(value+1.5*exports)
collapse (mean) expintensity,by(ID)

sort ID
save temp6.dta,replace


use "ihracat.dta",clear
keep if year==2010

rename sırano ID
rename sırano_donem year
rename kýymet exports

sort ulke
merge ulke using "crosswalk-country codes.dta" 
drop if _merge==2
drop if countrycode==""
drop if countrycode=="TUR"
drop _merge ulke

gen hs4=floor(HS12/100000000)
collapse (sum) exports,by(ID countrycode hs4)

bys ID ,sort: egen exports_total=sum(exports)
gen export_share=exports/exports_total

destring ID,replace
sort ID
merge ID using temp6.dta
keep if _m==3
drop _merge
capture erase temp6.dta

sort countrycode hs4
merge countrycode hs4 using temp4.dta
keep if _m==3
drop _merge
capture erase temp4.dta

egen unadjWID0=rowmean(unadjWID2011 unadjWID2012)
egen unadjWID1=rowmean(unadjWID2014 unadjWID2015)

egen adjWID0=rowmean(adjWID2011 adjWID2012)
egen adjWID1=rowmean(adjWID2014 adjWID2015)
egen gdpadjWID0=rowmean(gdpadjWID2011 gdpadjWID2012)
egen gdpadjWID1=rowmean(gdpadjWID2014 gdpadjWID2015)


gen unadjexshock=(unadjWID1-unadjWID0)*export_share*expintensity
gen adjexshock=(adjWID1-adjWID0)*export_share*expintensity
gen gdpadjexshock=(gdpadjWID1-gdpadjWID0)*export_share*expintensity
gen lngdppc_w_ex=lngdppc*export_share*expintensity

gen actualshock=adjWID1-adjWID0
su actualshock,det
gen mean=r(mean)
gen sd=r(sd)
gen randomshock= rnormal(mean,sd)

gen randomexshock=randomshock*export_share*expintensity

// Produces some of the summary statistics in Table A10 
egen v=group(countrycode hs4)
unique hs4,det
unique countrycode,det
unique v,det

bys v,sort: gen numfirm=_N
gen s=export_share*expintensity
su s,det
gen s_n=export_share*expintensity/numfirm
preserve
collapse (sum)  s_n,by(v)
gen HH=s_n^2
collapse (sum) HH
gen inv_HH=1/HH
restore

collapse (sum)  unadjexshock adjexshock gdpadjexshock lngdppc_w_ex randomexshock,by(ID)

destring ID,replace
sort ID 
save expshock.dta,replace


*Construct import shocks
use masterdata.dta,clear

keep if year==2010

keep if nace2_s<34

collapse (sum) value (mean) wagebill,by(buyer_ID)

gen rename buyer_ID ID
destring ID,replace

sort ID
merge ID using temp3
drop if _m==2
drop exports _m
capture erase temp3.dta

replace imports=0 if imports==.

gen impintensity=1.5*imports/(wagebill+value+1.5*imports)
collapse (mean) impintensity,by(ID)

sort ID
save temp6.dta,replace


use "ithalat.dta",clear
keep if year==2010

rename sırano ID
rename sırano_donem year
rename kýymet imports

sort ulke
merge ulke using "crosswalk-country codes.dta" 
drop if _merge==2
drop if countrycode==""
drop if countrycode=="TUR"
drop _merge ulke

gen hs4=floor(HS12/100000000)
collapse (sum) imports,by(ID countrycode hs4)

bys ID ,sort: egen imports_total=sum(imports)
gen import_share=imports/imports_total

destring ID,replace
sort ID
merge ID using temp6.dta
keep if _m==3
drop _merge
capture erase temp6.dta

sort countrycode hs4
merge countrycode hs4 using temp5.dta
keep if _m==3
drop _merge
capture erase temp5.dta

egen unadjWES0=rowmean(unadjWES2011 unadjWES2012)
egen unadjWES1=rowmean(unadjWES2014 unadjWES2015)

egen adjWES0=rowmean(adjWES2011 adjWES2012)
egen adjWES1=rowmean(adjWES2014 adjWES2015)

gen unadjimpshock=(unadjWES1-unadjWES0)*import_share*impintensity
gen adjimpshock=(adjWES1-adjWES0)*import_share*impintensity
gen lngdppc_w_im=lngdppc*import_share*impintensity

collapse (sum)  unadjimpshock adjimpshock lngdppc_w_im ,by(ID)

destring ID,replace
sort ID 
save impshock.dta,replace

// Construct masterdata for shift-share regressions //

*Construct annual exports
use "ihracat.dta",clear

rename sırano ID
rename sırano_donem year
rename kýymet exports

gen period=0 if year==2011 | year==2012
replace period=1 if year==2014 |  year==2015
drop if period==.

collapse (sum) exports,by(ID period)

reshape wide exports,i(ID) j(period)

for X in any 0 1 \ Y in any 1.73 2.45: replace exportsX=exportsX*Y // Convert exports to TRY using average ER

destring ID,replace
sort ID
save temp3.dta,replace

*Construct indegree and supplier wages
use masterdata.dta,clear

keep if nace2_b <34 // Keep manufacturing buyers
keep if nace2_s <34 // Keep manufacturing suppliers
drop if avgwage_adj_b==0 | avgwage_adj_b==.
drop if avgwage_adj_s==0 | avgwage_adj_s==.

gen period=0 if year==2011 | year==2012
replace period=1 if year==2014 |  year==2015
drop if period==.

for X in any avgwage_adj_s avgwage_adj_b: replace X=ln(X) 

collapse (sum) value (mean) avgwage_adj_b avgwage_adj_s,by(buyer_ID seller_ID period)

bys buyer_ID period,sort: egen total_value_b=sum(value)
gen value_share=value/total_value_b

gen supavgwage_w=avgwage_adj_s*value_share

bys buyer_ID period,sort: gen indegree=_N
bys buyer_ID period,sort: egen supavgwage_uw=mean(avgwage_adj_s)
gen supavgwage_cov=(avgwage_adj_s-supavgwage_uw)*(value_share-1/indegree)

collapse (mean) supavgwage_uw  indegree (sum) supavgwage_w supavgwage_cov,by(buyer_ID period )

reshape wide supavgwage_uw  indegree supavgwage_w supavgwage_cov,i(buyer_ID) j(period)

rename buyer_ID ID
destring ID,replace
sort ID 
merge ID using temp3.dta
drop if _m==2
drop _m
sort ID
save temp3.dta,replace

use wfe2.dta,clear

gen period=0 if year==2011 | year==2012
replace period=1 if year==2014 |  year==2015
drop if period==.

collapse (mean) w_fe,by(ID period)
reshape wide w_fe,i(ID) j(period)

sort ID
merge ID using temp3.dta
drop _m
sort ID
save temp3.dta,replace

*Construct KSW quality measure
use "ihracat.dta",clear

rename sırano ID
rename sırano_donem year
rename kýymet value
rename miktar quantity

gen period=0 if year==2011 | year==2012
replace period=1 if year==2014 |  year==2015
drop if period==.

gen hs8=floor(HS12/10000)
gen hs4=floor(HS12/100000000)

collapse (sum) value quantity,by(ID ulke hs8 hs4 year period)

gen sigma=5
gen lnuv=ln(value/quantity)
gen dep=ln(quantity)+sigma*lnuv

egen cp4y=group(ulke hs4 year)
egen py=group(hs8 year)

reghdfe dep ,absorb(cp4y py) residuals(q_r)

gen lnq= q_r/(sigma-1)

collapse (mean) lnq ,by(ID period)

reshape wide lnq,i(ID) j(period)

destring ID,replace
sort ID 
merge ID using temp3.dta
drop _m
sort ID
save temp3.dta,replace


*Construct indegree and supplier wages
use masterdata.dta,clear

gen period=0 if year==2011 | year==2012
replace period=1 if year==2014 |  year==2015
drop if period==.

bys seller_ID period,sort: egen domsales=sum(value)

keep if nace2_b <34 // Keep manufacturing buyers
keep if nace2_s <34 // Keep manufacturing suppliers
drop if avgwage_adj_b==0 | avgwage_adj_b==.
drop if avgwage_adj_s==0 | avgwage_adj_s==.

for X in any avgwage_adj_s avgwage_adj_b: replace X=ln(X) 

collapse (sum) value (mean) avgwage_adj_b avgwage_adj_s workers_s domsales,by(buyer_ID seller_ID period)

bys seller_ID period,sort: egen total_value_s=sum(value)
gen value_share=value/total_value_s

gen buyeravgwage_w=avgwage_adj_b*value_share

bys seller_ID period ,sort: gen outdegree=_N
bys seller_ID period ,sort: egen buyeravgwage_uw=mean(avgwage_adj_b)
gen buyeravgwage_cov=(avgwage_adj_b-buyeravgwage_uw)*(value_share-1/outdegree)

collapse (mean) avgwage_adj_s buyeravgwage_uw  workers_s outdegree domsales (sum) buyeravgwage_w buyeravgwage_cov,by(seller_ID nace4_s nace2_s province_code_s period)

reshape wide avgwage_adj_s buyeravgwage_uw  workers_s outdegree domsales buyeravgwage_w buyeravgwage_cov,i(seller_ID nace4_s nace2_s province_code_s) j(period)

gen ID=seller_ID
destring ID,replace
sort ID 
merge ID using temp3.dta
drop _m seller_ID
capture erase temp3.dta

forvalues i=0(1)1{
replace exports`i'=0 if exports`i'==.
gen expintensity`i'=exports`i'/(exports`i'+domsales)
}

foreach y of varlist indegree outdegree workers_s domsales {
replace `y'=ln(`y')	
}

foreach y of varlist avgwage_adj_s expintensity buyeravgwage_w buyeravgwage_cov supavgwage_uw   supavgwage_w supavgwage_cov indegree outdegree workers_s domsales w_fe lnq {
gen Chg`y'=`y'1-`y'0	
}

sort ID
merge ID using  expshock.dta
drop if _m==2
drop _m

sort ID
merge ID using  impshock.dta
drop if _m==2
drop _m

save shiftsharesample.dta,replace

